Equipe 10 :
1. Introduction
2. Le dictionnaire des données
3. Modèle Conceptuel de Données
4. Modèle Logique de Données
5. Modèle Physique de Données
1. Peuplement
2. Requêtes
3. Fonctionnalités
4. Conclusion
Pour rappel, le projet vise à concevoir une base de données centralisée pour stocker et analyser les données sur la qualité de l’air en France.
Phase 1 :
Nous devons livrer le dictionnaire des données, le MCD, le MLD, le script SQL et les arbres algébriques des différentes requêtes demandées.
Expliquer ce qu’est :
Le dictionnaire de données est l’ensemble des données de référence nécessaire à la création d’une base de donnée relationnelle.
Le modèle conceptuel de données est une représentation de manière abstraite et formelle de la structure d’une BDD.
Le modèle logique de données est simplement la traduction en script SQL de la modélisation. Il s’agit de la représentation en ligne du schéma représentant la structure de la base de données.
Le modèle physique de données est le script SQL final comprenant notamment les types et tailles des données.
Les arbres algébriques sont la représentation d’une requête SQL sous la forme d’un arbre. Les feuilles de l’arbre représentent les tables de départ.
Un dictionnaire de données joue un rôle essentiel dans la création d’une base de données en fournissant une description structurée et normalisée des éléments qui la composent. Il définit les informations sur les tables, les champs, les types de données et les relations. Ce référentiel centralisé facilite la cohérence, la maintenance, la documentation et la gouvernance des données, tout en assurant une communication claire entre tous les membres du projet et du client.
Le dictionnaire de données joue également un rôle important dans l’élaboration de la matrice des dépendances et du Modèle Conceptuel de Données (MCD).
Vous pourrez le trouver dans notre dossier ci-joint.
Définition : Le MCD est une représentation abstraite et indépendante des données d’un système d’information. Il permet de décrire les entités, les relations entre ces entités, et les règles qui les régissent.
Objectif : L’objectif principal du MCD est de fournir une vue globale et compréhensible des données manipulées par le système, sans se préoccuper des aspects techniques de stockage ou de gestion.
Éléments :
Nous avons schématiser le Modèle Conceptuel de Données (MCD) grâce à l’application Looping, qui permet de représenter graphiquement celui-ci, ainsi que faciliter les étapes de conception suivantes.

Notre MCD est composé de 10 tables et 19 associations.
Personnel
Adresse
Agence
Capteur
Gaz
Rapport
Relève
Dates
Poste
Ville
Habiter
Gérer
Travailler
Rédiger
Naître
Débuter
Dater
Se situer
Installer
Effectuer
Identifier
Rapporter
Localiser
Faire
Capter
Dater
Définition : Le MLD est une transformation du MCD en un modèle plus proche de la réalisation technique. Il traduit les concepts du MCD en structures de données qui peuvent être implémentées dans une base de données.
Objectif : Le MLD vise à organiser les données de manière à ce qu’elles puissent être stockées et manipulées efficacement dans un système de gestion de base de données (SGBD).
Éléments :
À partir du MCD, nous pouvons transformer celui-ci en MLD sur Looping, et celui-ci nous renvoie le MLD suivant sans erreur, nous pouvons donc conclure que celui-ci est correct.

À noter que plusieurs clés étrangères ne sont pas correctement nommées, comme id_date et id_date_1 dans la table Personnel. En effet, la conversion du MCD au MLD est automatique, donc le logiciel les a nommé par défaut. Nous ne pouvons pas les modifier donc nous les modifierons dans le script SQL.
Définition : Le MPD est la représentation concrète et technique des données, adaptée à un SGBD spécifique. Il décrit comment les données seront physiquement stockées et organisées dans la base de données.
Objectif : Le MPD a pour but d’optimiser les performances et l’efficacité du stockage et de l’accès aux données, en tenant compte des spécificités du SGBD utilisé.
Éléments :
Nous avons joint le Modèle Physique de Données (MPD) en document texte, nous avons construit celui-ci encore une fois grâce à l’aide de Looping.
Phase 2 :
Nous devons livrer les requêtes demandées (en langage SQL), des explications sur la génération et le peuplement de la base de données finale (origine des données, difficultés rencontrées) et les fonctionnalités présentes ou absentes par rapport à la demande et aux spécifications techniques.
Il nous a été demandé d’imaginer un jeu de données à intégrer dans la base de données pour tester et vérifier les fonctionnalités de celle-ci.
Pour cela, nous sommes partis sur la rédaction d’un script en langage Python, car cette méthode possède plusieurs avantages :
import random
import csv
from datetime import datetime, timedelta
# Initialisation des lignes SQL
sql_lignes = []
# Données à insérer
gaz_data = [
(1, "Protoxyde d azote", "N2O", "GES"),
(2, "Ozone troposphérique", "O3", "GES"),
(3, "Méthane", "CH4", "GES"),
(4, "Hydrofluorocarbures", "HFC", "GESI"),
(5, "Hexafluorure de soufre", "SF6", "GESI"),
(6, "Dioxyde de carbone", "CO2", "GES"),
(7, "Ammoniac", "NH3", "GESI"),
(8, "Perfluorocarbures", "PFC", "GES")
]
# Génération des lignes INSERT
for id_gaz, nom, sigle, typ in gaz_data:
ligne = f"INSERT INTO gaz (id_gaz, gaz_nom, gaz_sigle, gaz_type) VALUES ({id_gaz}, '{nom}', '{sigle}', '{typ}');"
sql_lignes.append(ligne)
Pour la génération des rues d’adresse, des noms et prénoms, ainsi que les diplôme du personnel, nous avons demandé à une intelligence artificielle de générer une liste de possibilités pour chaque, que nous avons associés aléatoirement pour obtenir un résultat réaliste, bien que contenant quelques anomalies comme des personnes possédant le même nom et prénom, cependant ces anomalies n’ont aucun impact grâce à l’utilisation d’un identifiant unique.
import random
nombre_de_villes = 4
nombre_de_rues_souhaites = 50
adresses = []
noms_rues = [
"Rue de la République",
"Avenue Victor Hugo",
"Boulevard Haussmann",
]
for i in range(nombre_de_rues_souhaites):
numero = random.randint(1, 200)
rue = random.choice(noms_rues)
id_ville = random.randint(1, nombre_de_villes)
adresses.append((f"{numero}", rue, id_ville))
# Affichage du résultat
print("adresses = [")
for adresse in adresses:
print(f" {adresse},")
print("]")
Pour s’assurer par exemple qu’un capteur relié à l’agence de Paris est bien géré par un agent technique de Paris, nous avons, lors de la création du personnel, ajouté chaque agent à une liste créée pour chaque ville, puis nous avons attribué un agent de cette liste aléatoirement à chaque capteur.
Ces données simulées nous ont permis de créer un environnement réaliste temporaire pour notre base de données, en fournissant des valeurs fictives.
Le fichier permettant de créer et peupler la BDD se nomme “scipt_complet.sql”, ce fichier est séparé en plusieurs fichiers plus petit dans le dossier “Scripts SQL” et possèdent l’ordre auxquels ils doivent être exécuté.
1. Liste de l’ensemble des agences
Select *
From Agence;
2. Liste de l’ensemble du personnel technique de l’agence de Bordeaux
Select id_personnel, pers_nom, pers_prenom
From Personnel
Join Agence On Agence.id_agence = Personnel.id_agence
Join Adresse On Adresse.id_adresse = Agence.id_adresse
Join Ville On Ville.id_ville = Adresse.id_ville
Where Ville.ville_nom = "Bordeaux";
3. Nombre total de capteurs déployés
Select Count(id_capteur)
From Capteur;
4. Liste des rapports publiés entre 2018 et 2022
Select *
From Rapport
Natural Join Dates
Where d_date Between "2018-01-01" And "2022-12-31";
5. Afficher les concentrations de CH4 (en ppm) dans les régions « Ile-de-France », « Bretagne » et « Occitanie » en mai et juin 2023
Select id_releve, rel_donnee
From Releve
Join Agence On Agence.id_agence = Releve.id_agence
Join Dates On Dates.id_date = Releve.id_date
Join Gaz On Gaz.id_gaz = Releve.id_gaz
Where Gaz.gaz_sigle = "CH4"
And Dates.d_date Between '2023-05-01' And '2023-06-30'
And Agence.ag_region In ("Île de France", "Bretagne","Occitanie");
6. Liste des noms des agents techniques maintenant des capteurs concernant les gaz à effet de serre provenant de l’industrie (GESI)
Select Distinct Personnel.id_personnel, Personnel.pers_nom, Personnel.pers_prenom
From Personnel
Join Poste On Poste.id_poste = Personnel.id_poste
Join Capteur On Capteur.id_personnel = Personnel.id_personnel
Join Gaz On Gaz.id_gaz = Capteur.id_gaz
Where Gaz.gaz_type = "GESI" And Poste.post_nom = "Assistés d agents techniques";
7. Titres et dates des rapports concernant des concentrations de NH3, classés par ordre anti-chronologique
Select Distinct rap_titre, d_date
From Rapport
Join Rapporter On Rapporter.id_rapport = Rapport.id_rapport
Join Releve On Releve.id_releve = Rapporter.id_releve
Join Gaz On Gaz.id_gaz = Releve.id_gaz
Join Dates On Dates.id_date = Rapport.id_date
Where Gaz.gaz_sigle = 'NH3'
Order By Dates.d_date Desc;
8. Afficher le mois où la concentration de PFC a été la moins importante pour chaque région
Select
ag_region As region,
d_date As mois
From Releve
Join Capteur On Capteur.id_capteur = Releve.id_capteur
Join Gaz On Gaz.id_gaz = Capteur.id_gaz
Join Agence On Agence.id_agence = Capteur.id_agence
Join Dates On Dates.id_date = Releve.id_date
Where gaz_sigle = 'PFC'
And (Agence.ag_region, rel_donnee) In (
Select
ag_region,
MIN(rel_donnee)
From Releve
Join Capteur On Capteur.id_capteur = Releve.id_capteur
Join Gaz On Gaz.id_gaz = Capteur.id_gaz
Join Agence On Agence.id_agence = Capteur.id_agence
Where gaz_sigle = 'PFC'
Group By Agence.ag_region
);
9. Moyenne des concentrations (en ppm) dans la région « Ile-de-France » en 2020, pour chaque gaz étudié
Select AVG(rel_donnee), Gaz.gaz_nom
From Releve
Join Agence On Agence.id_agence = Releve.id_agence
Join Dates On Dates.id_date = Releve.id_date
Join Gaz On Gaz.id_gaz = Releve.id_gaz
Where Agence.ag_region = "Île de France" And Dates.d_date Between '2020-01-01' And '2020-12-31'
Group By gaz_nom;
10. Taux de productivité des agents administratifs de l’agence de Toulouse (le taux est calculé en nombre de rapports écrits par mois en moyenne, sur la durée de leur contrat)
Select
pers_nom,
pers_prenom,
COUNT(Distinct id_rapport) / TIMESTAMPDIFF(MONTH, d_poste.d_date, CURDATE()) As productivite_mensuelle
From Personnel
Natural Join Adresse
Natural Join Ville
Natural Join Agence
Natural Join Poste
Join Dates As d_poste On Personnel.id_date_poste = d_poste.id_date
Left Join rediger USING(id_personnel)
Where ville_nom = "Toulouse"
And post_nom Like "%administratif%"
Group By id_personnel;
11. Pour un gaz donné, liste des rapports contenant des données qui le concernent (on doit pouvoir donner le nom du gaz en paramètre)
Set @nomGaz = "CO2";
SELECT DISTINCT rap.id_rapport, rap.rap_titre
FROM Gaz
Join Releve On Releve.id_gaz = Gaz.id_gaz
JOIN Rapporter On Rapporter.id_releve = Releve.id_releve
JOIN Rapport AS rap On rap.id_rapport = Rapporter.id_rapport
WHERE gaz_sigle = @nomGaz;
12. Liste des régions dans lesquelles il y a plus de capteurs que de personnel d’agence
Select ag_region
From (
Select ag_region, COUNT(id_capteur) As nb_capteurs
From Capteur
Natural Join Agence
Group By ag_region
) As CapteursParRegion
Natural Join (
Select ag_region, COUNT(id_personnel) As nb_personnel
From Personnel
Natural Join Agence
Group By ag_region
) As PersonnelParRegion
Where nb_capteurs > nb_personnel;
Pour créer des comptes utilisateurs, nous devons donner un nom au compte, ainsi qu’un mot de passe, si nécessaire. Nous devons aussi sélectionner ses droits d’accès.
Alors, nous devions créer 2 comptes utilisateurs pour les accès et les droits sur la base de données. Un compte Admin et un compte User :

Le compte Admin a accès à touts les droits de la base de données. Tandis que le compte User ne peut qu’afficher et insérer des enregistrements, ainsi que visualiser la base de données.
Voici les privilèges du compte User :

Les agents doivent être capable d’insérer des rapports dans la base de donnée.
Syntaxe générale de la requête pour y insérer des rapports :
Insert Into Rapport (rap_titre, rap_analyse, id_date, id_agence)
Values ('valeur1', 'valeur2', (Select id_date From Dates Where d_date = 'valeur3'), (Select id_agence From Agence Where ag_region = valeur4));
Avec :
Syntaxe générale de la requête pour mentionner le personnel et les relevés impliqués :
-- Mention des relevés
INSERT INTO rapporter (id_rapport, id_releve) VALUES (valeur5, 'valeur6');
-- Mention du personnel ayant rédigé le rapport
INSERT INTO rediger (id_rapport, id_personnel) VALUES (valeur5, valeur7);
Avec :
Nous avons pu concevoir une base de données qui répond à toutes les demandes. Cependant, quelques points d’amélioration peuvent encore être réalisé. En effet, dans le MCD, on pourrait rajouter une entitée “Type de gaz” pour éviter les redondances, dans l’entitée agence nous pourrions aussi rajouter un “nom d’agence”.
Ensuite, les requêtes auraient pu être plus optimisés, permettant une utilisation de la BDD ayant un impact réduit sur le climat, car des requêtes optimisées nécessitent moins de ressources de la part du server.
Puis lors du peuplement de la base de données, nous aurions pu insérer un plus grand nombre de données, pour tester plus en profondeur les nombreuses requêtes. Malgrés, ces points d’amélioration non réalisés, la base de données est bien fonctionnelle.